<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width = device-width, initial-scale = 1.0, minimum-scale = 1.0, maximum-scale = 1.0, user-scalable = no"/>
    <title>excel</title>
  </head>
  <body>
    <table id="domtoexcel" class="domtoexcel">
      <tr>
        <td>姓名</td>
        <td>电话</td>
        <td>邮箱</td>
      </tr>
      <tr>
        <td>狐狸</td>
        <td>123456</td>
        <td>123@123456.com</td>
      </tr>
      <tr>
        <td>美洲豹</td>
        <td>123456</td>
        <td>123@123456.com</td>
      </tr>
      <tr>
        <td>狮子</td>
        <td>123456</td>
        <td>123@123456.com</td>
      </tr>
      <tr>
        <td>蝴蝶</td>
        <td>123456789</td>
        <td>123@123456.com</td>
      </tr>
      <tr>
        <td>蜥蜴</td>
        <td>1234567891</td>
        <td>1235</td>
      </tr>
      <tr>
        <td>你会看到36.00%</td>
        <td>12345678912</td>
        <td style="vnd.ms-excel.numberformat:#0.00%">0.36</td>
      </tr>
      <tr>
        <td>本行有科学计数法</td>
        <td>123456789123</td>
        <td >20210625</td>
      </tr>
      <tr>
        <td>本行无科学计数法</td>
        <td style="color:#f00;vnd.ms-excel.numberformat:@;">1234567891234</td>
        <td>123@123456.com</td>
      </tr>
      <tr>
        <td>2个格子</td>
        <td>123123</td>
      </tr>
      <tr>
        <td colspan="2">阿斯达斯</td>
        <td></td>
        <td>个发广告</td>
      </tr>
    </table>
    <h3>第一种方法</h3>
    <button onclick="tableToExcel(document.getElementById('domtoexcel'))">getElementById导出</button>
    <button onclick="tableToExcel(document.querySelector('.domtoexcel'),{fileName:'滴滴滴'})">querySelector导出</button>
    <button onclick="tableToExcel(document.querySelectorAll('.domtoexcel')[0])">querySelectorAll导出</button>
    <div>小贴士:如果表格中的数字大于等于12位,会被excel自动转化成科学计数法,解决办法是给td的style加vnd.ms-excel.numberformat:@</div>
    <br /><br />
    <h3>第二种方法</h3>
    <button onclick="tableToExcel({tbHead,tbBody})">从json导出excel</button>

    <script>
      const tbHead = ["姓名", "电话", "邮箱"];
      // 要导出的json数据
      const tbBody = [{
          name: "张三",
          phone: "123456",
          email: "123@123456.com",
        },
        {
          name: "李四",
          phone: "123456",
          email: "123@123456.com",
        },
        {
          name: "王五",
          phone: "123456",
          email: "123@123456.com",
        },
        {
          name: "赵子龙",
          phone: "123456789",
          email: "123@123456.com",
        },
        {
          name: "麻花疼",
          phone: 1234567891,
          email: "123@123456.com",
        },
        {
          name: "马晕",
          phone: 12345678912,
          email: "123@123456.com",
        },
        {
          name: "杰克逊",
          phone: 123456789123,
          email: "123@123456.com",
        },
        {
          name: "ak47",
          phone: 1234567891234,
          email: "123@123456.com",
        },{aaa:99,asd:'',sd:'撒旦撒旦撒旦'}
      ];
      function tableToExcel(targetObj, options) {
        /*
         * targetObj <Object>|<HTMLElement> 必填
         * targetObj.tbHead <Array> 必填 形如['姓名','年龄']
         * targetObj.tbBody <Array> 必填 形如[{name:'ak',age:12},{name:'mp5',age:25}]
         * options <Object> 选填
         * options.workSheet <String> 非必填
         * options.fileName <String> 非必填
         */
        let strTable = "";
        if (targetObj instanceof HTMLElement) {
          strTable = targetObj.outerHTML;
        } else {
          targetObj = { ...targetObj };
          const tbHead = targetObj.tbHead || [];
          const tbBody = targetObj.tbBody || [];
          strTable = "<table>";
          strTable += renderHead(tbHead);
          strTable += renderBody(tbBody);
          strTable += "</table>";
        }
        options = { ...options };
        const workSheet = options.workSheet || "Sheet1";
        const fileName = options.fileName || new Date().getTime();
        const uri = "data:application/vnd.ms-excel;base64,";

        // 待下载的表格模板数据
        const template = `<html xmlns:o="urn:schemas-microsoft-com:office:office" 
        xmlns:x="urn:schemas-microsoft-com:office:excel" 
        xmlns="http://www.w3.org/TR/REC-html40">
        <head><meta charset="utf-8"><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>
        <x:Name>${workSheet}</x:Name>
        <x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet>
        </x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]-->
        </head><body>${strTable}</body></html>`;

        /*下载模板*/
        const link = document.createElement("a");
        link.href = uri + toBase64(template);
        link.download = fileName + ".xls";
        link.click();

        function toBase64(s) {
          return window.btoa(unescape(encodeURIComponent(s)));
        }
        function renderHead(arrHead) {
          //arrHead形如['姓名','邮箱','电话']
          var str = "<tr>";
          arrHead.forEach((v, k) => {
            str += `<td>${v}</td>`;
          });
          str += "</tr>";
          return str;
        }
        function renderBody(jsonBodyData) {
          //jsonBodyData形如[{name:'ak',age:12},{name:'mp5',age:25}]
          var str = "";
          jsonBodyData.forEach((v, k) => {
            str += "<tr>";
            for (const key in v) {
              if(typeof v[key]==='number' && v[key].toString().length>10){
                //其实12个数字才会变科学技术法
                str += `<td style='vnd.ms-excel.numberformat:@'>${v[key]}</td>`;
              }else{
                str += `<td>${v[key]}</td>`;
              }
            }
            str += "</tr>";
          });
          return str;
        }
      }
    </script>
  </body>
</html>